package com.mes.cloud.utils;

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.ss.usermodel.Workbook;
import org.jxls.area.Area;
import org.jxls.area.XlsArea;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.command.Command;
import org.jxls.command.EachCommand;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import org.jxls.util.TransformerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导出Excel工具类beta
 *
 * @author xuzq 2018-05-26 09:31
 */
public class ReportExcelUtil {

    /**
     * 设置响应前缀 下载文件的标识字段
     */
    private static final  String REPORT_HEADER = "Content-Disposition";

    /**
     * 设置响应后缀
     */
    private static final  String REPORT_HEADER_TWO = "attachment;filename=";

    /**
     * 导出为xlsx格式
     */
    public static final  String REPORT_XLSX = ".xlsx";

    /**
     * 导出为xls格式
     */
    public static final  String REPORT_XLS = ".xls";
    /**
     * 导出每页的大小
     */
    public static final int EXPORT_PAGE_SIZE = 1000;

    /**
     * 采用函数导出excel
     * @param is
     * @param response
     * @param reportFileName
     * @param reportSuffix
     * @param tempFileUrl
     * @param model
     * @throws IOException
     */
    public static void exportExcel(InputStream is, HttpServletResponse response,
                                   String reportFileName, String reportSuffix,String tempFileUrl,Map<String, Object> model) throws IOException {
        //配置下载路径
        createDir(new File(tempFileUrl));
        //根据模板生成新的excel
        File excelFile = createNewFile(model, is, tempFileUrl, reportFileName + reportSuffix);
        //浏览器端下载文件
        downloadFile(response, excelFile);
        //删除服务器生成文件
        deleteFile(excelFile);
    }

    /***
     * 采用注释导出excel
     * @param is
     * @param response
     * @param reportFileName
     * @param reportSuffix
     * @param tempFileUrl
     * @param model
     */
    public static void exportExcel2(InputStream is, HttpServletResponse response,
                                    String reportFileName, String reportSuffix,String tempFileUrl,Map<String, Object> model) throws IOException {
        response.setHeader(
                REPORT_HEADER,
                REPORT_HEADER_TWO
                        + URLEncoder.encode(reportFileName + reportSuffix,"UTF-8"));
        // Context context = new Context();
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        File newFile = new File(tempFileUrl + reportSuffix + reportSuffix);
        OutputStream os = new FileOutputStream(newFile);
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        //设置静默模式，不报警告
        //evaluator.getJexlEngine().setSilent(true);
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());    //添加自定义功能
        evaluator.getJexlEngine().setFunctions(funcs);
        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
        //浏览器端下载文件
        downloadFile(response, newFile);
        //删除服务器生成文件
        deleteFile(newFile);
    }
    //如果目录不存在创建目录 存在则不创建
    private static void createDir(File file) {
        if (!file.exists()) {
            file.mkdirs();
        }
    }
    /**
     * 根据excel模板生成新的excel
     *
     * @param beans
     * @param path
     * @return
     */
    private static File createNewFile(Map<String, Object> beans, InputStream is, String path,String name) {
        XLSTransformer transformer = new XLSTransformer();
        File newFile = new File(path + name);
        try {
            OutputStream out = new FileOutputStream(newFile);
            Workbook workbook = transformer.transformXLS(is, beans);
            workbook.write(out);
            out.flush();
            is.close();
            out.close();
            return newFile;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return newFile;
    }

    /**
     * 简单的List导出Excel文件
     *
     * @param response
     *            HttpServletResponse 输出响应
     * @param is
     *            InputStream
     *            读取模版文件的文件输入流，一般为“类.class.getResourceAsStream("模版名称.xlsx")”
     * @param reportFileName
     *            String 导出的文件名称
     * @param reportSuffix
     *            String 导出的文件后缀
     * @param reportList
     *            List 需要导出的List集合
     * @throws Exception
     */
    @SuppressWarnings("rawtypes")
    public static void reportExcelToList(HttpServletResponse response,
                                   InputStream is, String reportFileName, String reportSuffix,
                                   List reportList) throws IOException {
        response.setHeader(
                REPORT_HEADER,
                REPORT_HEADER_TWO
                        + URLEncoder.encode(reportFileName + reportSuffix,
                        "UTF-8"));
        Context context = new Context();
        context.putVar("reportList", reportList);
        reportToContext(response, is, reportFileName, reportSuffix, context);
    }

    
    /**
     * 简单的Map导出Excel文件
     *
     * @param response
     *            HttpServletResponse 输出响应
     * @param is
     *            InputStream
     *            读取模版文件的文件输入流，一般为“类.class.getResourceAsStream("模版名称.xlsx")”
     * @param reportFileName
     *            String 导出的文件名称
     * @param reportSuffix
     *            String 导出的文件后缀
     * @param reportMap
     *            HashMap 需要导出的Map集合
     * @throws IOException
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static void reportExcelToMap(HttpServletResponse response,
                                        InputStream is, String reportFileName, String reportSuffix,
                                        Map reportMap) throws IOException {
        Context context = new Context(reportMap);
        reportToContext(response, is, reportFileName, reportSuffix, context);
    }

    @SuppressWarnings("rawtypes")
    public static void reportExcelPage(HttpServletResponse response,
                                       InputStream is, String reportFileName,
                                       List reportList) throws IOException{
    	reportExcelPage(response,is,reportFileName,REPORT_XLSX,reportList,EXPORT_PAGE_SIZE);
    }
    /**
     * 分页导出Excel文件
     *
     * @param response       HttpServletResponse 输出响应
     * @param is             InputStream 读取模版文件的文件输入流，一般为“类.class.getResourceAsStream("模版名称.xlsx")”
     * @param reportFileName String 导出文件的名称
     * @param reportSuffix   String 导出文件的后缀
     * @param reportList     List 需要导出的List集合
     * @param pageSize       Integer 每页显示大小
     * @throws IOException
     */
    @SuppressWarnings("rawtypes")
    public static void reportExcelPage(HttpServletResponse response,
                                       InputStream is, String reportFileName, String reportSuffix,
                                       List reportList, Integer pageSize) throws IOException {
        response.setHeader(
                REPORT_HEADER,
                REPORT_HEADER_TWO
                        + URLEncoder.encode(reportFileName + reportSuffix,
                        "UTF-8"));
        OutputStream os = response.getOutputStream();
        Transformer transformer = TransformerFactory.createTransformer(is, os);
        AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
        List<Area> xlsAreaList = areaBuilder.build();
        Area xlsArea = xlsAreaList.get(0);
        Area perArea = xlsAreaList.get(1);

        XlsArea sheetArea = new XlsArea(xlsArea.getAreaRef(), transformer);
        EachCommand sheetEachCommand = new EachCommand("sheet", "sheets",
                sheetArea, new SimpleCellRefGenerator());
        xlsArea.addCommand(xlsArea.getAreaRef(), sheetEachCommand);

        XlsArea personArea = new XlsArea(perArea.getAreaRef(), transformer);
        Command personEachCommand = new EachCommand("obj", "sheet.reportList",
                personArea);
        sheetArea.addCommand(perArea.getAreaRef(), personEachCommand);

        Context context = new Context();
        List<ReportExcelList> sheetList = createReportExcelSheet(reportList,
                pageSize);
        context.putVar("reportList", sheetList);
        xlsArea.applyAt(xlsArea.getStartCellRef(), context);
        xlsArea.processFormulas();
        transformer.write();
        is.close();
        os.close();
    }

    /**
     * 将list集合转成分页list对象以便分页使用
     *
     * @param reportList List 需要导出的数据
     * @param pageSize   Integer 每个sheet的数据条数
     * @return List ReportExcelList泛型
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    private static List<ReportExcelList> createReportExcelSheet(
            List reportList, Integer pageSize) {
        Integer pageNumber = reportList.size() % pageSize == 0 ? reportList.size()
                / pageSize : ((reportList.size() / pageSize) + 1);
        List<ReportExcelList> lists = new ArrayList<ReportExcelList>();
        for (int i = 0; i < pageNumber; i++) {
            ReportExcelList reportExcelList = new ReportExcelList();
            if ((i + 1) == pageNumber) {
                reportExcelList.setReportList(reportList.subList(i * pageSize,
                        reportList.size()));
                lists.add(reportExcelList);
            } else {
                reportExcelList.setReportList(reportList.subList(i * pageSize, (i + 1)
                        * pageSize));
                lists.add(reportExcelList);
            }

        }
        return lists;
    }

    /**
     * 对于数据量不大的简单导出功能，提供公共的导出方法，只需要提供Context内容
     *
     * @param response
     *            HttpServletResponse 输出响应
     * @param is
     *            InputStream
     *            读取模版文件的文件输入流，一般为“类.class.getResourceAsStream("模版名称.xlsx")”
     * @param reportFileName
     *            String 导出的文件名称
     * @param reportSuffix
     *            String 导出的文件后缀
     * @param reportContext
     *            Context 输出到Excel上面的内容数据
     * @throws IOException
     */
    private static void reportToContext(HttpServletResponse response,
                                        InputStream is, String reportFileName, String reportSuffix,
                                        Context reportContext) throws IOException {
        // 设置响应
        response.setHeader(
                REPORT_HEADER,
                REPORT_HEADER_TWO
                        + URLEncoder.encode(reportFileName + reportSuffix,
                        "UTF-8"));
        // 设置输出流，这里使用response对象的输出流，提供web下载
        OutputStream os = response.getOutputStream();
        JxlsHelper.getInstance().processTemplate(is, os, reportContext);
        is.close();
        os.close();
    }
    /**
     * 将服务器新生成的excel从浏览器下载
     *
     * @param response
     * @param excelFile
     */
    private static void downloadFile(HttpServletResponse response, File excelFile) throws IOException{
        /* 设置文件ContentType类型，这样设置，会自动判断下载文件类型 */
        response.setContentType("multipart/form-data");
        /* 设置文件头：最后一个参数是设置下载文件名 */
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelFile.getName(),"UTF-8"));
        try (
                InputStream ins = new FileInputStream(excelFile);
                OutputStream os = response.getOutputStream()
        ) {
            byte[] b = new byte[1024];
            int len;
            while ((len = ins.read(b)) > 0) {
                os.write(b, 0, len);
            }
        } catch (IOException ioe) {
            ioe.printStackTrace();
        }
    }

    /**
     * 浏览器下载完成之后删除服务器生成的文件
     * 也可以设置定时任务去删除服务器文件
     *
     * @param excelFile
     */
    private static void deleteFile(File excelFile) {

        excelFile.delete();
    }
    /**
     * 将需要导出的数据转换成分页展示对象，为适应分页导出功能
     *
     * @param <T>
     */
    @SuppressWarnings("serial")
    public static class ReportExcelList<T> implements Serializable {
        /**
         * 需要转换的分页数据集合
         */
        private List<T> reportList;

        public List<T> getReportList() {
            return reportList;
        }

        public void setReportList(List<T> reportList) {
            this.reportList = reportList;
        }
    }

}
